#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
@file: area.py
@time: 2024-06-23 22:44
@desc:
"""
import traceback
from io import BytesIO
from flask import request, jsonify, Blueprint, send_file, url_for
from sqlalchemy import desc
from sqlalchemy.orm import joinedload

from common_tools import apply_formatting
from project_model import FieldStack, FieldArea, Image
from tools import log_operations, token_required, get_images_df, excel_to_images, excel_to_contacts, \
    excel_to_field_stacks
from global_scope import MAX_PAGE_SIZE, get_db, images_sheet_name
import pandas as pd
from tools import get_area_df, get_contacts_df, get_field_stack_df
from tools import excel_to_areas
from global_scope import areas_sheet_name, field_stacks_sheet_name, contacts_sheet_name

db = get_db()

field_area_bp = Blueprint('field_area', __name__, url_prefix='/field_area')


@field_area_bp.route('', methods=['POST'])
@log_operations
@token_required
def add_field_area():
    data = request.json
    name = data.get('name')
    remark = data.get('remark')
    field_stack_id = data.get('field_stack_id')
    location = data.get('location', '场内')
    container_length = data.get('container_length', '20英尺')
    max_capacity = data.get('max_capacity')
    image_ids = data.get('image_ids', [])

    if not name or not field_stack_id:
        return jsonify({"code": 1, "message": "Name and field_stack_id are required"}), 400

    field_area = FieldArea.query.filter_by(name=name, field_stack_id=field_stack_id).first()
    if not field_area:
        field_area = FieldArea(
            name=name,
            remark=remark,
            field_stack_id=field_stack_id,
            location=location,
            container_length=container_length,
            max_capacity=max_capacity
        )
        db.session.add(field_area)
        db.session.commit()

    # 处理 image_ids 字段
    for image_id in image_ids:
        image = Image.query.get(image_id)
        if image and image not in field_area.images:
            field_area.images.append(image)

    db.session.commit()

    return jsonify({"code": 0, "message": "Field area added successfully", "data": field_area.id}), 201


@field_area_bp.route('/<int:area_id>', methods=['PATCH'])
@log_operations
@token_required
def update_field_area(area_id):
    data = request.json
    field_area = FieldArea.query.get(area_id)
    if not field_area:
        return jsonify({"code": 1, "message": "Field area not found"}), 404

    if 'name' in data:
        field_area.name = data['name']
    if 'remark' in data:
        field_area.remark = data['remark']
    if 'field_stack_id' in data:
        field_area.field_stack_id = data['field_stack_id']
    if 'location' in data:
        field_area.location = data['location']
    if 'container_length' in data:
        field_area.container_length = data['container_length']
    if 'max_capacity' in data:
        field_area.max_capacity = data['max_capacity']
    if 'images' in data:
        image_ids = [c.get('id') for c in data['images'] if c.get('id')]
        images = Image.query.filter(Image.id.in_(image_ids)).all()
        field_area.images = images

    db.session.commit()

    return jsonify({"code": 0, "message": "Field area updated successfully"}), 200


@field_area_bp.route('/<int:area_id>', methods=['DELETE'])
@log_operations
@token_required
def delete_field_area(area_id):
    field_area = FieldArea.query.get(area_id)
    if not field_area:
        return jsonify({"code": 1, "message": "Field area not found"}), 404

    db.session.delete(field_area)
    db.session.commit()

    return jsonify({"code": 0, "message": "Field area deleted successfully"}), 200


@field_area_bp.route('/query', methods=['POST'])
@log_operations
@token_required
def get_field_areas():
    data = request.json or dict()
    name = data.get('name')
    remark = data.get('remark')
    sizes = data.get('sizes')
    fieldStackIds = data.get('fieldStackIds')
    locations = data.get('locations')
    page_no = int(data.get('pageNo', 1))  # 默认第一页
    page_size = int(data.get('pageSize', MAX_PAGE_SIZE))  # 默认每页MAX_PAGE_SIZE条记录

    query = db.session.query(FieldArea).join(FieldStack).options(joinedload(FieldArea.images))

    if name:
        query = query.filter(FieldArea.name.contains(name))
    if remark:
        query = query.filter(FieldArea.remark.contains(remark))

    if fieldStackIds:
        query = query.filter(FieldArea.field_stack_id.in_(fieldStackIds))

    if locations:
        query = query.filter(FieldArea.location.in_(locations))

    if sizes:
        query = query.filter(FieldArea.container_length.in_(sizes))

    # 对结果进行排序，例如按创建时间降序排列
    query = query.order_by(desc(FieldArea.id))

    # 执行分页查询
    pagination = query.paginate(page=page_no, per_page=page_size, error_out=False)
    field_areas = pagination.items

    data = []
    for fa in field_areas:
        images = [{'id': img.id, 'imageUrl': img.imageUrl} for img in fa.images]
        data.append({
            "id": fa.id,
            "name": fa.name,
            "remark": fa.remark,
            "field_stack_name": fa.field_stack.name,
            "field_stack_address": fa.field_stack.address,
            "field_stack_id": fa.field_stack_id,
            "location": fa.location,
            "container_length": fa.container_length,
            "max_capacity": fa.max_capacity,
            "images": images,
            "createTime": fa.createTime.isoformat(),
            "updateTime": fa.updateTime.isoformat(),
            "fs_value": f"{fa.field_stack.name} | {fa.field_stack.address}",
            "fullName": f"{fa.name}| {fa.container_length} | {fa.field_stack.name} | {fa.field_stack.address}"
        })

    # 构造响应体，包括分页数据和总数
    response_data = {
        "pageData": data,
        "total": pagination.total,
    }

    # 构造原始的URL，包括查询参数
    origin_url = url_for('field_area.get_field_areas', **request.args.to_dict())

    return jsonify({
        "code": 0,
        "message": "OK",
        "data": response_data,
        "originUrl": origin_url
    }), 200


@field_area_bp.route('/download', methods=['GET'])
@log_operations
@token_required
def download_field_areas():
    output = BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df_objs = [
            (get_images_df(), images_sheet_name),
            (get_contacts_df(), contacts_sheet_name),
            (get_field_stack_df(), field_stacks_sheet_name),
            (get_area_df(), areas_sheet_name)
        ]
        # 写入数据
        for df, sheet_name in df_objs:
            df.to_excel(writer, index=False, sheet_name=sheet_name)
            apply_formatting(writer.sheets[sheet_name], df)
    output.seek(0)

    return send_file(output, download_name="field_areas.xlsx", as_attachment=True)


@field_area_bp.route('/upload', methods=['POST'])
@log_operations
@token_required
def upload_field_areas():
    if 'file' not in request.files:
        return jsonify({"code": 1, "message": "No file part"}), 400

    file = request.files['file']
    if file.filename == '':
        return jsonify({"code": 1, "message": "No selected file"}), 400

    try:
        excel_to_images(file, request)
        excel_to_contacts(file, request)
        excel_to_field_stacks(file, request)
        new_records, updated_records = excel_to_areas(file, request)
        return jsonify({
            "code": 0,
            "message": f"Field areas uploaded successfully: {new_records} added, {updated_records} updated"
        }), 201
    except Exception as e:
        tb = traceback.format_exc()
        request.trace_logger.error(f"Exception occurred: {str(e)}\n{tb}")
        return jsonify({"code": 1, "message": str(e)}), 500
